var express = require('express');
var router = express.Router();
const uuid = require("uuid");
const order = require("../sql/Order")
const exchangeRate = require("../sql/ExchangeRate")
const customer = require("../sql/Customer")
const user = require("../sql/User");
const xlsx = require("node-xlsx");
const path = require("path");
const fs = require("fs");
const getMonthDate = require("./untils/getMonthDate");

router.get('/', function(req, res, next) {

  const date = getMonthDate();

  const { employeesName } = req.session.userData;
  Promise.all([ order.find({},{_id:0,__v:0}), user.find({},{_id:0,__v:0}), customer.find({},{_id:0,__v:0}), exchangeRate.find({},{_id:0,__v:0}) ]).then(data=>{
    let orderData = data[0];
    const s = new Date(date[0]);
    const e = new Date(date[1]);
    orderData = data[0].filter(val=>{
      const n = new Date(dateToLocal(val.OrderDate||"")).getTime();
      return n >= s.getTime() && n <= e.getTime();
    })
    const od = orderData.map(val=>{
      return {
        ...val._doc,
        OrderDate: dateToLocal(val.OrderDate),
        CustomerName: data[2].find(v=>val.CustomerName===v.cId) || {}
      }
    });
    res.render('order', {
      employeesName,
      orderData: od,
      userData: data[1],
      exchangeRateData: data[3],
      index: 1
    });
  }).catch(code=>{
    console.error(code);
    res.send( code );
  })
});

// 搜索（按日期）
router.get('/search', function(req, res, next) {
  const { employeesName } = req.session.userData;
  const {startDate, endDate} = req.query;

  Promise.all([ order.find({},{_id:0,__v:0}), user.find({},{_id:0,__v:0}), customer.find({},{_id:0,__v:0}), exchangeRate.find({},{_id:0,__v:0}) ]).then(data=>{
    let orderData = data[0];
    if( startDate && endDate ){
      const s = new Date(dateToLocal(startDate));
      const e = new Date(dateToLocal(endDate));
      orderData = data[0].filter(val=>{
        const n = new Date(dateToLocal(val.OrderDate||"")).getTime();
        return n >= s.getTime() && n <= e.getTime();
      })
    }
    const od = orderData.map(val=>{
      return {
        ...val._doc,
        OrderDate: dateToLocal(val.OrderDate),
        CustomerName: data[2].find(v=>val.CustomerName===v.cId) || {}
      }
    });
    res.render('order', {
      employeesName,
      orderData: od,
      userData: data[1],
      exchangeRateData: data[3],
      index: 1
    });
  }).catch(code=>{
    console.error(code);
    res.send( code );
  })
});

// 添加页面
router.get('/add', function(req, res, next) {
  const { employeesName } = req.session.userData;

  Promise.all([ exchangeRate.find(), customer.find(), order.find() ]).then(data=>{
    res.render('orderAdd', {
      employeesName,
      exchangeRateData: data[0],
      customerData: data[1],
      orderData: data[2],
      index: 1
    });
  }).catch(code=>{
    console.error(code);
    res.send( code );
  })
});

// 添加处理
router.post('/add/handle', async function(req, res, next) {
  const data = req.body;
  try {
    const exchangeRateData = await exchangeRate.find({},{_id:0,__v:0});
    const er = exchangeRateData.find(v=>data.Currency===v.eId).exchangeRate;
    const orderData = await order.find({AccountingNo: data.AccountingNo});
    if(orderData.length > 0){
      await order.updateMany( { AccountingNo: data.AccountingNo }, {
        OriginalCurrencyAmount: ((orderData[0].OriginalCurrencyAmount-0) + (data.OriginalCurrencyAmount-0)).toFixed(2)-0,
        OriginalCurrencyAmountAfterDiscount: ((orderData[0].OriginalCurrencyAmountAfterDiscount-0) + (data.OriginalCurrencyAmountAfterDiscount-0)).toFixed(2)-0,
        LocalCurrencyAmount: (((orderData[0].OriginalCurrencyAmount-0) + (data.OriginalCurrencyAmount-0)) * er).toFixed(2)-0,
        LocalCurrencyAmountAfterDiscount: (((orderData[0].OriginalCurrencyAmountAfterDiscount-0) + (data.OriginalCurrencyAmountAfterDiscount-0)) * er).toFixed(2)-0
      } );
    }else{
      if( !data.CustomerName.startsWith("customer-") ){
        const customerData = await customer.find({},{_id:0,__v:0});
        const cData = customerData.find(v=>v.cName===data.CustomerName) || {};
        data.CustomerName = cData.cId;
      }

      await order.insertMany( {
        oId: 'order-'+uuid.v1(),                                // 唯一标志
        ...data,
        LocalCurrencyAmount: ((data.OriginalCurrencyAmount-0) * er).toFixed(2)-0,
        LocalCurrencyAmountAfterDiscount: ((data.OriginalCurrencyAmountAfterDiscount-0) * er).toFixed(2)-0
      } );
    }

    res.redirect('/order');

  } catch (error) {
    console.error(error);
    res.send( {code:0, msg:'error', data:error} )
  }
});

// 修改订单流程人员
router.post('/setUser', async function(req, res, next) {
  const { oId, type, uId } = req.body;
  
  const updateData = {};

  let userName = '';
  if(uId){
    try {
      const userData = await user.find({uId});
      userName = userData[0].employeesName;
    } catch (code) {
      res.send( {code:0, msg:'error', data:error} )
    }
  }
  
  updateData[type] = userName;

  order.updateOne( {oId}, updateData ).then(()=>{
    res.send( {code:1, msg:'success', data: { uId, userName }} );
  }).catch(code=>{
    res.send( {code:0, msg:'error', data:code} );
  })
});

// 修改订单基本信息
router.post('/set', async function(req, res, next) {
  // 客户，船名，港口，原币金额，原币折扣后金额
  let { oId, AccountingNo, CustomerName, ShipName, ShipPort, Currency, OriginalCurrencyAmount, OriginalCurrencyAmountAfterDiscount } = req.body;

  if( !CustomerName.startsWith("customer-") ){
    const customerData = await customer.find({},{_id:0,__v:0});
    const cData = customerData.find(v=>v.cName===CustomerName) || {};
    CustomerName = cData.cId;
  }

  try {
    const exchangeRateData = await exchangeRate.find({},{_id:0,__v:0});
    const er = exchangeRateData.find(v=>Currency===v.eId).exchangeRate;

    const o = await order.find({ AccountingNo });
    if(o.length > 0 && o[0].oId !== oId){
      await order.deleteOne( { oId } );
      await order.updateOne( { AccountingNo }, {
        AccountingNo, CustomerName, ShipName, ShipPort, Currency, 
        OriginalCurrencyAmount: ((o[0].OriginalCurrencyAmount-0) + (OriginalCurrencyAmount-0)).toFixed(2)-0,
        OriginalCurrencyAmountAfterDiscount: ((o[0].OriginalCurrencyAmountAfterDiscount-0) + (OriginalCurrencyAmountAfterDiscount-0)).toFixed(2)-0,
        LocalCurrencyAmount: (((o[0].OriginalCurrencyAmount-0) + (OriginalCurrencyAmount-0)) * er).toFixed(2)-0,
        LocalCurrencyAmountAfterDiscount: (((o[0].OriginalCurrencyAmountAfterDiscount-0) + (OriginalCurrencyAmountAfterDiscount-0)) * er).toFixed(2)-0
      } );
    }else{
      await order.updateOne({ oId }, {
        AccountingNo, CustomerName, ShipName, ShipPort, Currency, OriginalCurrencyAmount, OriginalCurrencyAmountAfterDiscount, 
        LocalCurrencyAmount: ((OriginalCurrencyAmount-0) * er).toFixed(2)-0,
        LocalCurrencyAmountAfterDiscount: ((OriginalCurrencyAmountAfterDiscount-0) * er).toFixed(2)-0
      });
    }
    res.redirect('/order');
  } catch (error) {
    console.error(error);
    res.send( {code:0, msg:'error', data:error} )
  }
})

// 导出订单
router.get('/export', function(req, res, next) {
  const {startDate, endDate} = req.query;
  let excelData = [{
    name: "订单数据",
    data:[
      ["订单日期", "核算号", "客户名称", "船名", "港口", "币种", "订单原币金额", "订单本币金额", "原币折扣后金额", "本币折扣后金额", "订单员", "市场员", "报价员", "采购员", "仓库员", "供船员", "财务员", "物流员", "注意！！！本表不能用作导入！！！"],
    ]
  }];
  res.setHeader('Content-Disposition', 'attachment; filename=order' + new Date().toLocaleDateString() + Date.now() + '.xlsx');
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

  Promise.all([ order.find({},{_id:0,__v:0}), customer.find(), exchangeRate.find() ]).then(data=>{
    let oData = data[0];
    if( startDate && endDate ){
      const s = new Date(dateToLocal(startDate));
      const e = new Date(dateToLocal(endDate));
      oData = data[0].filter(val=>{
        const n = new Date(dateToLocal(val.OrderDate)).getTime();
        return n >= s.getTime() && n <= e.getTime();
      })
    }
    oData.forEach(val=>{
      const CustomerName = data[1].find(v=>v.cId===val.CustomerName)?.cName;
      const Currency = data[2].find(v=>v.eId===val.Currency)?.currency;
      excelData[0].data.push([
        val.OrderDate, val.AccountingNo, CustomerName, val.ShipName, val.ShipPort, Currency, val.OriginalCurrencyAmount, val.LocalCurrencyAmount, val.OriginalCurrencyAmountAfterDiscount, val.LocalCurrencyAmountAfterDiscount, val.OrderClerk, val.Marketer, val.Quoter, val.Purchasing, val.Warehouse, val.ProvideBoat, val.Financial, val.Logistics
      ])
    })
    const buffer = xlsx.build(excelData);
    res.send( buffer );
  }).catch(code=>{
    console.error( code );
    const buffer = xlsx.build(excelData);
    res.send( buffer );
  })
});

// 导出模板
router.get('/exportTemp', function(req, res, next) {
  let excelData = [{
    name: "订单数据",
    data:[
      ["订单日期", "核算号", "客户名称", "船名", "港口", "币种", "订单原币金额", "原币折扣后金额", "订单员", "市场员", "报价员", "采购员", "仓库员", "供船员", "财务员", "物流员", "注意！！！导入时请不要对表结构做任何修改！！！"],
    ]
  }];
  const buffer = xlsx.build(excelData);
  res.setHeader('Content-Disposition', 'attachment; filename=order_template.xlsx');
  res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  res.send( buffer );
});

// 导入订单
router.post('/import', async function(req, res, next) {
  const file = req.files[0];
  const xlsxPath = path.join(__dirname, "..", file.path);
  if( file ){
    try {
      const exchangeRateData = await exchangeRate.find({},{_id:0,__v:0});
      const customerData = await customer.find({},{_id:0,__v:0});
      const data = xlsx.parse( xlsxPath )[0].data;
      const arr = [];
      for(let i=1; i<data.length; i++){
        if(data[i].length>1){
          const eId = exchangeRateData.find(v=>v.currency===data[i][5]?.trim()).eId;
          const cData = customerData.find(v=>(data[i][2]?.trim() || '')===v.cName) || {};

          const dateNum = Number(data[i][0]);
          let d;
          if(dateNum){
            const oDate = new Date((dateNum-2)* 24 * 3600000);
            d = (oDate.getFullYear()-70) + "-" + (oDate.getMonth()+1) + "-" + oDate.getDate();
          }else{
            const oDate = new Date();
            d = oDate.getFullYear() + "-" + (oDate.getMonth()+1) + "-" + oDate.getDate();
          }

          arr.push({
            oId: 'order-'+uuid.v1(),      // 唯一标志
            OrderDate: d,        // 订单日期
            AccountingNo: data[i][1]?.trim(),     // 核算号
            CustomerName: cData.cId,      // 客户名称
            ShipName: data[i][3] || '',   // 船名
            ShipPort: data[i][4] || '',   // 港口
            Currency: eId,    // 币种
            OriginalCurrencyAmount: data[i][6].toFixed(2)-0 || 0,    // 原币金额
            OriginalCurrencyAmountAfterDiscount: data[i][7].toFixed(2)-0 || 0,   // 原币折扣后金额
            OrderClerk: data[i][8]?.trim() || '',     // 订单员
            Marketer: data[i][9]?.trim() || '',       // 市场员
            Quoter: data[i][10]?.trim() || '',        // 报价员
            Purchasing: data[i][11]?.trim() || '',    // 采购员
            Warehouse: data[i][12]?.trim() || '',     // 仓库员
            ProvideBoat: data[i][13]?.trim() || '',   // 供船员
            Financial: data[i][14]?.trim() || '',     // 财务员
            Logistics: data[i][15]?.trim() || '',     // 物流员
          })  
        }
      }
      // 重复订单号合并金额
      const repeatConcatData = repeatConcat( arr );

      // 删除文件
      fs.unlinkSync( xlsxPath );

      // 查询库中数据，检测是否出现重复
      for(let i=0; i<repeatConcatData.length; i++){
        const val = repeatConcatData[i];
        const orderData = await order.find( { AccountingNo: val.AccountingNo } );
        const exchangeRate = exchangeRateData.find(v=>val.Currency===v.eId).exchangeRate;
        if(orderData.length > 0){
          await order.updateMany( { AccountingNo: val.AccountingNo }, {
            OriginalCurrencyAmount: ((orderData[0].OriginalCurrencyAmount-0) + (val.OriginalCurrencyAmount-0)).toFixed(2)-0,
            OriginalCurrencyAmountAfterDiscount: ((orderData[0].OriginalCurrencyAmountAfterDiscount-0) + (val.OriginalCurrencyAmountAfterDiscount-0)).toFixed(2)-0,
            LocalCurrencyAmount: (((orderData[0].OriginalCurrencyAmount-0) + (val.OriginalCurrencyAmount-0)) * exchangeRate).toFixed(2)-0,
            LocalCurrencyAmountAfterDiscount: (((orderData[0].OriginalCurrencyAmountAfterDiscount-0) + (val.OriginalCurrencyAmountAfterDiscount-0)) * exchangeRate).toFixed(2)-0
          } );
        }else{
          await order.insertMany( {
            ...val,
            LocalCurrencyAmount: ((val.OriginalCurrencyAmount-0) * exchangeRate).toFixed(2)-0,
            LocalCurrencyAmountAfterDiscount: ((val.OriginalCurrencyAmountAfterDiscount-0) * exchangeRate).toFixed(2)-0
          } );
        }
      }

      res.redirect("/order");

    } catch (error) {
      console.error( error );
      fs.unlink( xlsxPath, ()=>{
        res.redirect('/order');
      } )
    }
  }else{
    fs.unlink( xlsxPath, ()=>{
      res.redirect('/order');
    } )
  }
});

function repeatConcat( data ){
  const arr = [];
  data.forEach(val=>{
    if(arr.find(v=>v.AccountingNo===val.AccountingNo)){
      const index = arr.findIndex(v=>v.AccountingNo===val.AccountingNo);
      arr[index].OriginalCurrencyAmount = ((arr[index].OriginalCurrencyAmount-0) + (val.OriginalCurrencyAmount-0)).toFixed(2)-0;
      arr[index].OriginalCurrencyAmountAfterDiscount = ((arr[index].OriginalCurrencyAmountAfterDiscount-0) + (val.OriginalCurrencyAmountAfterDiscount-0)).toFixed(2)-0;
    }else{
      arr.push( val );
    }
  })
  return arr;
}

// 锁定数据
router.get('/locking', async function(req, res, next) {
  const { oId, startDate, endDate, state} = req.query;
  if( oId ){
    order.updateOne( {oId}, {locking: Boolean(state-0)} ).then(()=>{
      res.redirect('/order');
    }).catch(code=>{
      console.error(code);
      res.redirect('/order');
    })
  }else if( !oId && startDate && endDate ){
    const s = new Date(dateToLocal(startDate));
    const e = new Date(dateToLocal(endDate));
    order.find({},{_id:0,__v:0}).then(orderData=>{
      const oIds = orderData.filter(val=>{
        const n = new Date(dateToLocal(val.OrderDate)).getTime();
        return n >= s.getTime() && n <= e.getTime();
      }).map(v=>({oId: v.oId}));

      order.updateMany( {$or: oIds}, {locking: Boolean(state-0)} ).then((c)=>{
        res.redirect('/order');
      }).catch(code=>{
        console.error(code);
        res.redirect('/order');
      })

    }).catch(code=>{
      console.error(code);
      res.redirect('/order');
    })
  }else {
    res.send( '请选择日期范围后，再做数据锁定' );
  }

});

// 删除订单
router.get('/delete', function(req, res, next) {
  const {oId} = req.query;
  order.deleteOne({oId}).then(()=>{
    res.redirect('/order');
  }).catch(code=>{
    res.redirect('/order');
    console.error(code);
  })
});

function dateToLocal(d){
  return d.split("-").map(v=>v-0).join("/")
}

module.exports = router;
